package com.hnkywl.sts.dao.jkgl;

import com.hnkywl.sts.entity.jkgl.XsOrder;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.SQLQuery;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 销售系统订单信息相关Dao
 * Created by Fei on 2015/12/22.
 */
@Repository
public class XsOrderDao extends SimpleHibernateTemplate<XsOrder, Long> {

    /**
     * 统计销售系统挂牌量
     *
     * @param dateStart
     * @param dateEnd
     * @return
     */
    public List<Map> countOrderWtByDate(String dateStart, String dateEnd) {
        String query = "SELECT SUM(ORDER_WT) as ORDER_WT,SUM(ORDER_AMT) as ORDER_AMT FROM T_XS_ORDER_INFO WHERE 1=1";
        String whereSql = "";
        Map<String, Object> param = new HashMap<String, Object>();

        if (!"".equals(dateStart)) {
            whereSql += " AND SIGN_DATE >= :dateStart";
            param.put("dateStart", dateStart.replace("-", ""));
        }

        if (!"".equals(dateEnd)) {
            whereSql += " AND SIGN_DATE < :dateEnd";
            param.put("dateEnd", dateEnd.replace("-", ""));
        }

        SQLQuery sqlQuery = getSession().createSQLQuery(query + whereSql);
        sqlQuery.setProperties(param);
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return sqlQuery.list();
    }

    /** 查询销售合同信息 */
    public XsOrder getByOrderItemNum(String orderItemNum) {
        String hql = " from XsOrder where orderItemNum = ?";
        return (XsOrder) findUnique(hql, orderItemNum);
    }
//    /** 查询最新的一页数据给交易大屏 */
//    public List<Map<String, Object>> getXSOrderDetail(String dateStart, String dateEnd) {
//        String query = "select substr(create_date,9,2)||':'||substr(create_date,11,2) \"orderTime\"," +
//                "COMPANY_NAME \"companyName\",ORDER_ITEM_NUM \"orderItemNum\",ORDER_WT \"orderWt\",ORDER_AMT \"orderAmt\" " +
//                "from (select * from T_XS_ORDER_INFO  WHERE 1=1";
//        String whereSql = "";
//        Map<String, Object> param = new HashMap<String, Object>();
//
//        if(!"".equals(dateStart)){
//            whereSql += " AND SIGN_DATE >= :dateStart";
//            param.put("dateStart",dateStart.replace("-", ""));
//        }
//
//        if(!"".equals(dateEnd)){
//            whereSql += " AND SIGN_DATE < :dateEnd";
//            param.put("dateEnd",dateEnd.replace("-", ""));
//        }
//        whereSql += " order by create_date desc,id desc) where rownum <= 4";
//        SQLQuery sqlQuery = getSession().createSQLQuery(query + whereSql);
//        sqlQuery.setProperties(param);
//        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
//        return sqlQuery.list();
//    }

    /** 查询指定日期的最新的一页数据给交易大屏 */
    public List<Map<String, Object>> getXSOrderItems(String orderDate) {
//        String query = "select (to_date(CREATE_DATE,'yyyymmddhh24miss')-to_date('19700101','yyyymmdd'))*1000*60*60*24 \"orderTime\"," +
        String query = "select substr(create_date,9,2)||':'||substr(create_date,11,2) \"orderTime\"," +
                    "COMPANY_NAME \"companyName\",BUY_USER_NAME \"buyerName\",create_date  \"createTime\"," +
                "ORDER_ITEM_NUM \"orderItemNum\",ORDER_WT \"orderWt\",ORDER_AMT \"orderAmt\"," +
                "PROD_NAME \"prodName\" from (select * from T_XS_ORDER_INFO";
        String whereSql = "";
        String rowLimitStr = "20";
        Map<String, Object> param = new HashMap<String, Object>();
        if(!"".equals(orderDate)){
            if (orderDate.length() >= 8) {
                whereSql += "  WHERE SIGN_DATE = :orderDate";
                param.put("orderDate", orderDate.replace("-", ""));
            } else {//当作行数条件来用
                rowLimitStr = orderDate;
                whereSql += " t where not exists(select 0 from T_XS_ORDER_INFO a" +
                        " where a.id>t.id and a.BUY_USER_NAME=t.BUY_USER_NAME)";//每个买家只显示一单
            }
        }
        whereSql += " order by create_date desc,id desc) where rownum <= " + rowLimitStr;
        SQLQuery sqlQuery = getSession().createSQLQuery(query + whereSql);
        sqlQuery.setProperties(param);
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return sqlQuery.list();
    }
}
